package daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc.DBUtil;
import dao.EmpDao;
import entity.Emp;



public class EmpDaoJdbcImpl implements EmpDao {
	
	public Emp findByUsername(String userName) throws Exception{
		String sql="select * from emp1 where name=?";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setString(1, userName);
			rs=pst.executeQuery();
			if(!rs.next())
				return null;
			Emp e = new Emp();
			return e;
		} finally {
			DBUtil.close(null, pst, rs);
			DBUtil.closeInThread();
		}
	}

	public void delete(int id) throws Exception {
		Connection con=null;
		try {
			String sql="delete from emp1 where id="+id;
			con=DBUtil.openInThread();
			Statement stm=con.createStatement();
			stm.execute(sql);
			stm.close();
		} finally {
			DBUtil.closeInThread();
		}
	}

	public void deleteByIds(int[] ids) throws Exception {
	}

	public List<Emp> findAll() throws Exception {
		List<Emp> list = new ArrayList<Emp>();
		String sql="select * from emp1";
		Connection con=null;
		Statement stm=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			stm=con.createStatement();
			rs=stm.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				double sal=rs.getDouble("sal");
				int age = rs.getInt("age");
				Emp e = new Emp();
				e.setId(id);
				e.setName(name);
				e.setSalary(sal);
				e.setAge(age);
				list.add(e);
			}
			DBUtil.close(null, stm, rs);
			return list;
		} finally {
			DBUtil.closeInThread();
		}
	}

	public Emp findById(int id) throws Exception {
		String sql="select * from emp1 where id="+id;
		Connection con=null;
		Statement stm=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			stm=con.createStatement();
			rs=stm.executeQuery(sql);
			if(!rs.next())
				return null;
			id=rs.getInt("id");
			String name=rs.getString("name");
			double sal=rs.getDouble("sal");
			int age=rs.getInt("age");
			Emp e = new Emp();
			e.setId(id);
			e.setName(name);
			e.setSalary(sal);
			e.setAge(age);
			return e;
		} finally {
			DBUtil.close(null, stm, rs);
			DBUtil.closeInThread();
		}
	}

	public void save(Emp emp) throws Exception {
		String name = emp.getName();
		double sal = emp.getSalary();
		int age = emp.getAge();
		
		Connection con = null;
		PreparedStatement pst = null;
		String sql="insert into emp1 ( name,sal,age) values(?,?,?)";
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setDouble(2, sal);
			pst.setInt(3, age);
			pst.execute();
			DBUtil.close(null, pst, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeInThread();
		}
	}

	public void update(Emp emp) throws Exception {
		int id = emp.getId();
		String name = emp.getName();
		double sal = emp.getSalary();
		int age=emp.getAge();
		
		Connection con = null;
		PreparedStatement pst = null;
		String sql="update emp1 set name=?,sal=?,age=? where id=?";
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setDouble(2, sal);
			pst.setInt(3, age);
			pst.setInt(4, id);
			pst.execute();
			DBUtil.close(null, pst, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeInThread();
		}
	}

}
